package com.aternity.geolocations;

import au.com.bytecode.opencsv.CSVWriter;
import com.aternity.common.geolocation.PolygonFormatter;
import com.aternity.common.geom.Polygon2D;
import com.google.common.base.CharMatcher;

import java.awt.geom.Rectangle2D;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * @author ddd
 * @since 11/27/13 2:30 PM
 */
public class FirebirdPg {

    static String myQuery = "select countrysyn.\"Name\" as \"country\", statesyn.\"Name\" as \"state\", citysyn.\"Name\" as \"city\"  \n" +
            "from \n" +
            "    \"City\" city\n" +
            "    join \"CitySynonyms\" citysyn on citysyn.\"ParentID\"=city.ID\n" +
            "    join \"State\" state on city.\"ParentID\"=state.ID\n" +
            "    join \"StateSynonyms\" statesyn on state.ID=statesyn.\"ParentID\"\n" +
            "    join \"Country\" country on state.\"ParentID\"=country.ID\n" +
            "    join \"CountrySynonyms\" countrysyn on country.ID=countrysyn.\"ParentID\"\n" +
            "where\n" +
            "    citysyn.\"Locale\"='en_US' and\n" +
            "    statesyn.\"Locale\"='en_US' and\n" +
            "    countrysyn.\"Locale\"='en_US'";


    static final String countryGeometry =
            "select \n" +
                    "    distinct country.ID, max(countrysyn.\"Name\") as \"country\"\n" +
                    "    , country.ISO3166_2, ldc.\"Geometry\" as geom\n" +
                    "from \n" +
                    "    \"Country\" country \n" +
                    "    join \"CountrySynonyms\" countrysyn on country.ID=countrysyn.\"ParentID\"\n" +
                    "    join \"LocalDataCountry\" ldc on ldc.\"ParentID\"=country.ID\n" +
                    "  where\n" +
                    "      (countrysyn.\"Locale\"='en_US' or countrysyn.\"Locale\" is NULL)\n" +
                    "      and countrysyn.\"IsDisplayName\" = 1\n" +
                    "group by\n" +
                    "    country.ID, country.ISO3166_2, geom\n" +
                    "order by\n" +
                    "    \"country\", country.ID";

    static final String stateGeometry =
            "select \n" +
                    "    distinct \n" +
                    "        country.ID as \"countryId\", \n" +
                    "        country.ISO3166_2, \n" +
                    "        max(countrysyn.\"Name\") as \"country\", \n" +
                    "        state.ID as \"stateId\" ,\n" +
                    "        statesyn.\"Name\" as \"State\",  \n" +
                    "        lds.\"Geometry\" as geom\n" +
                    "from \n" +
                    "    \"State\" state\n" +
                    "    join \"StateSynonyms\" statesyn on state.ID=statesyn.\"ParentID\"\n" +
                    "    join \"Country\" country on state.\"ParentID\"=country.ID\n" +
                    "    join \"CountrySynonyms\" countrysyn on country.ID=countrysyn.\"ParentID\"\n" +
                    "    join \"LocalDataState\" lds on lds.\"ParentID\"=state.ID\n" +
                    "  where\n" +
                    "      (countrysyn.\"Locale\"='en_US' or countrysyn.\"Locale\" is NULL)\n" +
                    "      and countrysyn.\"IsDisplayName\" = 1\n" +
                    "      and (statesyn.\"Locale\"='en_US' or statesyn.\"Locale\" is NULL)\n" +
                    "      and statesyn.\"IsDisplayName\" = 1\n" +
                    "group by\n" +
                    "    country.ID, country.ISO3166_2, state.ID, \"State\", lds.\"Geometry\"\n" +
                    "order by\n" +
                    "    \"country\", country.ID, state.ID";

    public static void main(String[] args) throws Exception {
        System.out.println(myQuery);
        Class.forName("org.firebirdsql.jdbc.FBDriver");
        Properties props = new Properties();
        props.setProperty("user", "SYSDBA");
        props.setProperty("password", "masterkey");
        props.setProperty("encoding", "UNICODE_FSS");
        Connection connection = DriverManager.getConnection("jdbc:firebirdsql:localhost/3050:C:/dev/geoloc/GEOCODING.fdb", props);
        readCountryPolygons(connection);
        readStatePolygons(connection);
    }

    static PolygonFormatter polygonFormatter = new PolygonFormatter();
    static RectangleFormatter rectangleFormatter = new RectangleFormatter();

    static void readCountryPolygons(Connection connection) throws Exception {
        try (CSVWriter csvWriter = new CSVWriter(new OutputStreamWriter(new FileOutputStream("country.geom.csv"), "UTF-8"))) {
            final PreparedStatement ps = connection.prepareStatement(countryGeometry);
            final ResultSet rs = ps.executeQuery();


            while (rs.next()) {
                int countryId = rs.getInt(1);
                String countryName = rs.getString(2);
                String iso3166_3 = rs.getString(3);
                String geom = rs.getString(4);
                System.out.println(countryId + ", " + countryName + ", " + iso3166_3 + ", " + geom);
                String boundariesStr = geom == null ? "" : extractBoundaries(polygonFormatter, rectangleFormatter, geom);
                if (geom == null) {
                    geom = "";
                }
                csvWriter.writeNext(new String[]{Integer.toString(countryId), countryName, iso3166_3, geom, boundariesStr});
            }
        }
    }

    static void readStatePolygons(Connection connection) throws Exception {
        try (CSVWriter csvWriter = new CSVWriter(new OutputStreamWriter(new FileOutputStream("state.geom.csv"), "UTF-8"))) {
            final PreparedStatement ps = connection.prepareStatement(stateGeometry);
            final ResultSet rs = ps.executeQuery();

            StateRowInfo riBucket = null;

            while (rs.next()) {
                StateRowInfo ri = new StateRowInfo();
                ri.countryId = rs.getInt(1);
                ri.iso3166_3 = rs.getString(2);
                ri.countryName = rs.getString(3);
                ri.stateId = rs.getInt(4);
                ri.stateName = rs.getString(5);
                if("Central".equals(ri.stateName)){
                    System.out.println();
                }
                ri.geom = rs.getString(6);

                if (ri.geom == null) {
                    ri.geom = "";
                }
                if (riBucket == null) {
                    riBucket = ri;
                } else if (riBucket.stateId == ri.stateId) {
                    // if current value for state name contains non ascii characters
                    if (!CharMatcher.ASCII.matchesAllOf(riBucket.stateName)) {
                        riBucket.stateName = ri.stateName;
                    }
                } else {
                    writeStateDataCsvLine(csvWriter, riBucket);
                    riBucket = ri;
                }
            }
        }
    }

    private static void writeStateDataCsvLine(CSVWriter csvWriter, StateRowInfo ri) {
        String boundariesStr = ri.geom == null ? "" : extractBoundaries(polygonFormatter, rectangleFormatter, ri.geom);
        csvWriter.writeNext(new String[]{Integer.toString(
                ri.countryId), ri.countryName, ri.iso3166_3, Integer.toString(ri.stateId), ri.stateName, ri.geom, boundariesStr});
    }

    private static String extractBoundaries(PolygonFormatter polygonFormatter, RectangleFormatter rectangleFormatter, String geom) {
        List<Polygon2D> countryPolygons = polygonFormatter.parse(geom);
        List<Rectangle2D> boundaries = new ArrayList<>();
        for (Polygon2D polygon : countryPolygons) {
            boundaries.add(polygon.getBounds2D());
        }
        return rectangleFormatter.formatMultiRectangles(boundaries);
    }

    private static class StateRowInfo {
        int countryId;
        String iso3166_3;
        String countryName;
        int stateId;
        String stateName;
        String geom;
    }

}
